﻿// ?2008 IDesign Inc. All rights reserved 
//Questions? Comments? go to 
//http://www.idesign.net

using System;
using System.ServiceModel;
using System.Diagnostics;
using System.Collections.Generic;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.OracleClient;
using System.Data.Common;
[ServiceContract]
interface IIntexReptServBL
{

    [OperationContract]
    DataSet GetDataSetByQuery(Dictionary<string, object> dicSrcParamater);
    [OperationContract]
    DataSet RequestAgentStart(Dictionary<string, object> dicSrcParamater);
}
[ServiceBehavior(
    ConcurrencyMode = ConcurrencyMode.Reentrant,
    InstanceContextMode = InstanceContextMode.Single)]
public partial class IntexReptServBL : IIntexReptServBL
{
    Dictionary<string, Agent> AgentList = new Dictionary<string, Agent>();
    DataTable tbAgent;
    Int64 agents = 0;
    public IntexReptServBL()
    {
        tbAgent = new DataTable();
        tbAgent.Columns.Add("AGENTID", typeof(string));
        AgentList.Add("AGENTID-1", new Agent("-1"));
    }
    public Agent GetAgent(Dictionary<string, object> dicSrcParamater)
    {
        Agent agent = null;
        try
        {
            if (dicSrcParamater.ContainsKey("AGENTID"))
            {
                agent = AgentList[(string)dicSrcParamater["AGENTID"]];
            }
                /*
            else
            {
                agent = AgentList["AGENTID-1"];
            }*/
        }
        catch (Exception ex)
        {

        }
        return agent;
    }
    public DataSet GetDataSetByQuery(Dictionary<string, object> dicSrcParamater)
    {
        Agent agent = GetAgent(dicSrcParamater);
        DataSet dsResult = new DataSet();
        if (agent == null)
            return dsResult;
        try
        {
            dsResult = agent.GetDataSetByQuery(dicSrcParamater);
        }
        catch (Exception ex)
        {
            System.Console.Write(ex.StackTrace, ex);

        }
        return dsResult;
    }
    public DataSet RequestAgentStart(Dictionary<string, object> dicSrcParamater)
    {
        DataSet dsResult = new DataSet();
        Agent agent = null;
        string agentid = "";
        try
        {
            if (dicSrcParamater.ContainsKey("QAGENT"))
            {
                agentid = (string)dicSrcParamater["QAGENT"];
                AgentList[agentid] = null;
                AgentList.Remove(agentid);
                return dsResult;
            }
            agents++;
            agent = new Agent(agents.ToString());
            AgentList.Add("AGENTID" + agents, agent);
            DataTable tb = tbAgent.Copy();
            DataRow row = tb.NewRow();
            row["AGENTID"] = "AGENTID" + agents;
            tb.Rows.Add(row);
            dsResult.Merge(tb);
        }
        catch (Exception ex)
        {
        }
        finally
        {
            System.GC.Collect();
        }
        return dsResult;
    }
}
public class Agent
{
    public Agent()
    {
    }
    public Agent(string id)
    {
        ID = "AGETNID" + id;
    }
    public string ID;
    public SqlCommand sql;
    public DataSet dsResult;
    public SqlDataAdapter mySqlDa;
    public OracleCommand catCMD;
    public OracleDataAdapter myDa;
    public void SetUpOracle(Dictionary<string, object> dicSrcParamater)
    {
        string conn = "";
        conn = (string)dicSrcParamater["ConnectionString"];
        if (catCMD != null)
        {
            int pc = catCMD.Parameters.Count;
            for (int i = 0; i < pc; i++)
            {
                catCMD.Parameters.RemoveAt(0);
            }
            if (catCMD.Connection.ConnectionString.Trim() == conn.Trim())
            {
                if (catCMD.Connection.State == ConnectionState.Closed)
                {
                    catCMD.Connection.Open();
                }

                return;
            }
        }
        catCMD = new OracleCommand();
        catCMD.CommandType = CommandType.Text;
        foreach (KeyValuePair<string, object> kvp in dicSrcParamater)
        {
            switch (kvp.Key)
            {
                case "DBTYPE":
                    break;
                case "AGENTID":
                    break;
                case "CommandText":
                    catCMD.CommandText = (string)dicSrcParamater["CommandText"];
                    break;
                case "ConnectionString":
                    conn = (string)dicSrcParamater["ConnectionString"];
                    if (catCMD.Connection == null)
                    {
                        catCMD.Connection = new OracleConnection(conn);
                    }
                    if (catCMD.Connection.State == ConnectionState.Closed)
                        catCMD.Connection.Open();
                    break;
                case "CommandType":
                    break;
                default:
                    break;
            }
        }
    }
    public void SetUpSQLServer(Dictionary<string, object> dicSrcParamater)
    {

        string conn = (string)dicSrcParamater["ConnectionString"];
        if (sql != null)
        {
            int pcx = sql.Parameters.Count;
            for (int i = 0; i < pcx; i++)
            {
                sql.Parameters.RemoveAt(0);
            }
            if (sql.Connection.ConnectionString.Trim() == conn.Trim())
            {
                if (sql.Connection.State == ConnectionState.Closed)
                {
                    sql.Connection.Open();
                }
                return;
            }
        }
        sql = new SqlCommand();
        sql.CommandType = CommandType.Text;
        foreach (KeyValuePair<string, object> kvp in dicSrcParamater)
        {
            switch (kvp.Key)
            {
                case "DBTYPE":
                    break;
                case "AGENTID":
                    break;
                case "CommandText":                
                    break;
                case "ConnectionString":
                    conn = (string)dicSrcParamater["ConnectionString"];
                    sql.Connection = new SqlConnection(conn);
                    if (sql.Connection.State == ConnectionState.Closed)
                        sql.Connection.Open();
                    break;
                case "CommandType":                  
                    break;
                default:
                    break;
            }
        }
    }
    public DataSet GetDataSetByQuery(Dictionary<string, object> dicSrcParamater)
    {
        DataSet dsResult = new DataSet();
        try
        {
            string commandtype = "CommandText";
            string strCommandType;
            string dbType = "Oracle";
            if (dicSrcParamater.ContainsKey("DBTYPE"))
            {
                dbType = dicSrcParamater["DBTYPE"].ToString();
            }
            SqlParameter sqlParameter;
            switch (dbType)
            {
                case "SQLSERVER":
                    SetUpSQLServer(dicSrcParamater);
                    foreach (KeyValuePair<string, object> kvp in dicSrcParamater)
                    {
                        switch (kvp.Key)
                        {
                            case "DBTYPE":
                            case "AGENTID":
                            case "ConnectionString":                          
                                break;
                            case "CommandType":
                                strCommandType = (string)dicSrcParamater["CommandType"];
                                switch (strCommandType)
                                { 
                                    case "StoredProcedure":
                                        sql.CommandType = CommandType.StoredProcedure;
                                        commandtype = "StoredProcedure";
                                        break;
                                    case "TableDirect":
                                        sql.CommandType = CommandType.TableDirect;
                                        commandtype = "TableDirect";
                                        break;
                                    default:
                                        sql.CommandType = CommandType.Text;
                                        break;
                                }
                                break;
                            case "CommandText":
                                sql.CommandText = (string)dicSrcParamater["CommandText"];
                                break;                         
                            default:
                                sqlParameter = new SqlParameter();
                                sqlParameter.ParameterName = kvp.Key;
                                sqlParameter.Value = (string)kvp.Value;
                                sql.Parameters.Add(sqlParameter);
                                break;
                        }
                    }
                    if (mySqlDa == null)
                    {
                        mySqlDa = new SqlDataAdapter(sql);
                    }
                    mySqlDa.Fill(dsResult);          
                    break;
                case "ORACLE":
                default:
                    SetUpOracle(dicSrcParamater);
                    OracleParameter catCMDP;              
                    foreach (KeyValuePair<string, object> kvp in dicSrcParamater)
                    {
                        switch (kvp.Key)
                        {
                            case "DBTYPE":
                            case "AGENTID":
                            case "ConnectionString":                            
                                break;
                            case "CommandType":
                                strCommandType = (string)dicSrcParamater["CommandType"];
                                switch (strCommandType)
                                { 
                                    case "StoredProcedure":
                                        catCMD.CommandType = CommandType.StoredProcedure;
                                        commandtype = "StoredProcedure";
                                        break;
                                    case "TableDirect":
                                        catCMD.CommandType = CommandType.TableDirect;
                                        commandtype = "TableDirect";
                                        break;
                                    default:
                                        catCMD.CommandType = CommandType.Text;
                                        break;
                                }
                                break;
                            case "CommandText":
                                catCMD.CommandText = (string)dicSrcParamater["CommandText"];
                                break;
                            default:
                                catCMDP = new OracleParameter();
                                catCMDP.ParameterName = kvp.Key;
                                catCMDP.Value = (string)kvp.Value;
                                catCMD.Parameters.Add(catCMDP);
                                break;
                        }
                    }
                    if (commandtype == "StoredProcedure")
                    {
                        //Tracer.Verbose("Service", "StoredProcedure");
                        //MessageBox.Show("Dear Sir," + "StoredProcedure");
                        catCMD.ExecuteNonQuery();
                        return dsResult;
                    }
                    if (myDa == null)
                    {
                        myDa = new OracleDataAdapter(catCMD);
                        myDa.ContinueUpdateOnError = true;
                    }
                    myDa.Fill(dsResult);
                    break;
            }
        }
        catch (Exception ex)
        {
            System.Console.Write(ex.StackTrace, ex);
        }
        finally
        {


        }
        return dsResult;
    }
}